When analyzing data stored in a database, due to the way the database rules are formulated, a user may not receive the expected results that the user desires for a query. For example, a user may want to do an “ABC” classification of the company's products. The user creates a query with a key figure (a measure or a value/quantity field that has a restriction) of revenue of the products in a drill-down. Also, the user creates a formula of “Revenue<1000” to determine products with a revenue of less than 1000. A relational operator delivers “1” for true and “0” for false. The user may also want to know how many products have a revenue less than 1000. The user then adds to the formula the aggregation of “SUM(over Product)” to count how many products have a revenue less than 1000. The following table summarizes the results:
TABLE 1ProductRevenueRevenue <1000Admete 450 EUR1Agam1200 EUR0Grand Total1650 EUR1
As can be seen in Table 1, a grand total of products that have a revenue of less than 1000 is “1”. An exception aggregation is used to make sure the grand total of 1 is calculated correctly. For example, a reference characteristic of “Product” assures that the values from the grand total are grouped by “Product”. That is, the number of products is counted that have revenue less than 1000 in the grand total.
The user may also want to enhance the query to find out the top products and make the query more flexible. For example, the user may want to determine how many products have revenue greater than 1% of the total revenue. To determine this, the user uses a concept of constant selection to create a key figure of “Revenue CS Product” that determines the total revenue for all products (and not just one product). The constant selection operator is denoted by “CS” in the formula and removes the global filter, such as a “group-by” for the reference characteristic of Product in the calculation. Then, another formula calculates 1% of the total revenue and the last relational operator “>” is added. The example is summarized as follows in Table 2:
TABLE 21% ofRevenue >1%Revenue CSRevenue CSof RevenueProductRevenueProductProductCS ProductAdmete 450 EUR100.000 EUR1000 EUR1Agam1200 EUR100.000 EUR1000 EUR1Grand1650 EUR100.000 EUR1000 EUR2Total
As can be seen above in Table 2, the grand total of “2” is not what the user intended. However, the grand total is calculated correctly according to the rules followed by the database system. In the example, the product Admete has a revenue of 450 that is not greater than 1% of the total product revenue of 1000 EUR. Thus, the value should be “0” instead of “1”. However, due to how the database evaluates the rules, the value for Admete is “1”, and the grand total of “2” is received instead of “1”. The following summarizes the query for Table 2.SUM(Revenue>(0.01*(Revenue CS Product)) over Product
The intention of the query should be to count how many products have a revenue of more than 1% of the total revenue over all products. However, due to a principle of late aggregation, the analytical processing system (Online analytic processing system (OLAP system) does not evaluate the formula as the user desires. Rather, the constant selection (CS) on the characteristic of Product in “Revenue CS Product” removes the filter of the characteristic of Product, but the forced “group-by” from the exception aggregation “SUM” takes priority over the filter removal from CS due to the characteristic of Product being associated with the later calculation of “SUM”. In this case, the revenue of the specific product is used instead of the revenue of all products. For example, the database system evaluates the formula for the Admete product as follows:SUM,450>(0.01*450) (in this case,450 is always greater than 4.5).
In the above formula, the revenue for Admete product is used as the value for the key figure of Revenue CS Product instead of the total revenue of all products. This is because the analytical processing system does not honor the constant selection of Product in the key figure. Rather, the database system just uses the revenue of the Admete product without filtering a group by on the reference characteristic of Product.